package com.suoluo.zenadmin.dao.home;

import java.util.List;
import java.util.Map;

import net.paoding.rose.jade.annotation.DAO;
import net.paoding.rose.jade.annotation.SQL;
import net.paoding.rose.jade.annotation.SQLParam;

import com.suoluo.zenadmin.vo.dest.DestFeatureCategoryVO;
import com.suoluo.zenadmin.vo.home.HomeCategoryVO;
import com.suoluo.zenadmin.vo.home.HomeItemVO;

@DAO
public interface HomeDAO {

	@SQL("SELECT * FROM tb_home_category")
	public List<HomeCategoryVO> findListHomeCategory();

	@SQL("insert into tb_home_category(t_feature_category_id,t_category_name) "
			+ "values(:1.featureCategoryId,:1.categoryName)")
	public void saveHomeCategory(HomeCategoryVO categoryPOJO);

	@SQL("select * from tb_home_category where t_category_id=:1")
	public HomeCategoryVO findHomeCategoryById(int categoryId);

	@SQL("update tb_home_category set t_category_name=:1.categoryName "
			+ "where t_category_id=:1.categoryId")
	public void updateHomeCategory(HomeCategoryVO categoryPOJO);

	@SQL("delete from tb_home_item where t_category_id=:1")
	public void deleteHomeItemByCategoryId(int categoryId);

	@SQL("delete from tb_home_category where t_category_id=:1")
	public void deleteHomeCategory(int categoryId);
	@SQL("select * from tb_dest_feature_category d "
			+ "where d.t_feature_parent_category_id = 0 ")
	public List<DestFeatureCategoryVO> findListFeatureCategory();


	@SuppressWarnings("rawtypes")
	@SQL("select tb.t_category_name categoryName,"
			+ "th.t_item_create_time itemCreateTime,th.t_item_id itemId,"
			+ "tc.t_country_name countryName,tnews.zhname title,"
			+ "tnews.summary,ti.source,ti.small "
			+ "from tb_home_item th  join tb_home_category tb on "
			+ "tb.t_category_id = th.t_category_id join "
			+ "tb_news2 tnews on tnews.news_id=th.t_link_id "
			+ " join tb_country tc on tc.t_country_id = th.t_country_id "
			+ " join tb_image ti on ti.image_id = tnews.image_id "
			+ "where th.t_category_id =:1")
	public List<Map> findListNewsHomeItem(int categoryId);

	@SuppressWarnings("rawtypes")
	@SQL("select h.t_item_id itemId,n.news_id linkId,n.zhname title,"
			+ "if(isnull(h.t_link_id),0,1) link,ti.source,ti.small,"
			+ "n.country_id countryId "
			+ "from tb_news2 n left join  tb_home_item h "
			+ "on h.t_link_id = n.news_id  and h.t_category_id ="
			+ "(select t_category_id from tb_home_category where t_feature_category_id=:3) "
			+ "join tb_image ti on ti.image_id = n.image_id "
			+ "#if(:tempName != null){  where n.zhname like :tempName}  "
			+ " order by n.news_id desc limit :1,:2")
	public List<Map> findNewsHome(int startPage, int rows, int homeCategoryId,
			@SQLParam("tempName") String tempName);

	@SQL("select count(*) from tb_news2 h #if(:tempName != null){  where h.zhname like :tempName}")
	public int findNewsCount(@SQLParam("tempName") String tempName);

	@SuppressWarnings("rawtypes")
	@SQL("select h.t_item_id itemId,d.dest_id linkId,d.country_id countryId,"
			+ "d.zhname title,ti.source,ti.small,d.dest_id destId,"
			+ "if(isnull(h.t_link_id),0,1) link "
			+ " from  tb_dest d left join tb_home_item h"
			+ " on d.dest_id = h.t_link_id and d.dest_enable = 1 "
			+ "and h.t_category_id ="
			+ "(select t_category_id from tb_home_category where t_feature_category_id=:3) " +
					"join tb_image ti on ti.image_id=d.image_id "
			+ " where d.dest_enable = 1 #if(:tempName != null){  and d.zhname like :tempName}  "
			+ "  order by d.dest_id desc limit :1,:2")
	public List<Map> findDestHomeImg(int startPage, int rows,
			int homeCategoryId, @SQLParam("tempName") String tempName);

	@SQL("select count(*) from tb_dest d "
			+ " where d.dest_enable = 1 #if(:tempName != null){  and d.zhname like :tempName}  ")
	public int findDestCount(@SQLParam("tempName") String tempName);

	@SuppressWarnings("rawtypes")
	@SQL("select h.t_item_id itemId,ht.hotel_id linkId,ht.zhname title,"
			+ "ti.source,ti.small,"
			+ "d.country_id countryId,"
			+ "ht.dest_id destId,"
			+ "if(isnull(h.t_link_id),0,1) link"
			+ " from  tb_hotel2 ht left join tb_dest d on ht.dest_id=d.dest_id "
			+ "left join tb_home_item h"
			+ " on ht.hotel_id = h.t_link_id and h.t_category_id ="
			+ "(select t_category_id from tb_home_category where t_feature_category_id=:3) " +
					" join tb_image ti on ti.image_id=ht.image_id  "
			+ " #if(:tempName != null){ where d.zhname like :tempName } "
			+ " order by ht.hotel_id desc limit :1,:2")
	public List<Map> findHotelHome(int startPage, int rows, int homeCategoryId,
			@SQLParam("tempName") String tempName);

	@SQL("select count(*) from tb_hotel2 h join tb_dest d "
			+ "on d.dest_id = h.dest_id #if(:tempName != null){ where d.zhname like :tempName}")
	public int findHotelCount(@SQLParam("tempName") String tempName);

	@SuppressWarnings("rawtypes")
	@SQL("select td.feature_item_id linkId,ti.t_item_id itemId,"
			+ "ttd.dest_id destId,ttd.country_id countryId,"
			+ "td.zhname title,if(isnull(ti.t_link_id),0,1) link,"
			+ "timage.source,timage.small  "
			+ "from  tb_dest ttd "
			+ "join tb_dest_feature2 tf on tf.dest_id = ttd.dest_id "
			+ "join tb_dest_feature_category tdf on tdf.t_feature_category_id = tf.feature_category_id "
			+ "join tb_dest_feature_item2 td on td.feature_id=tf.feature_id " +
					"join tb_image timage on timage.image_id = td.image_id "
			+ "left join tb_home_item ti on ti.t_link_id = td.feature_item_id "
			+ "where (tdf.t_feature_category_id =:4 or tdf.t_feature_parent_category_id = :4) and td.deleted = 0 "
			+ "#if(:tempName != null){ and ttd.zhname like :tempName} "
			+ " order by linkId desc limit :1,:2")
	public List<Map> findFeatureItemByFeatureCategoryId(int startPage,
			int rows, @SQLParam("tempName") String tempName,
			int featureCategoryId);

	@SQL("select count(ti.feature_item_id) from tb_dest_feature2 tf "
			+ "join tb_dest ttd on ttd.dest_id = tf.dest_id "
			+ "join tb_dest_feature_category tdf on tdf.t_feature_category_id = tf.feature_category_id "
			+ "join tb_dest_feature_item2 ti on ti.feature_id=tf.feature_id "
			+ "where (tdf.t_feature_category_id =:1 or tdf.t_feature_parent_category_id = :1) and ti.deleted = 0 "
			+ "#if(:tempName != null){ and ttd.zhname like :tempName} ")
	public int findFeatureCategoryCount(int featureCategoryId,
			@SQLParam("tempName") String tempName);

	@SQL("delete from tb_home_item where t_item_id=:1")
	public void deleteHomeItem(int itemId);

	@SQL("insert into tb_home_item(t_category_id,t_dest_id,t_link_id,t_item_create_time,"
			+ "t_country_id) values(:1.categoryId,:1.destId,:1.linkId,:1.itemCreateTime,:1.countryId)")
	public void saveHomeItem(HomeItemVO item);

	@SuppressWarnings("rawtypes")
	@SQL("select tb.t_category_name categoryName,"
			+ "th.t_item_create_time itemCreateTime,th.t_item_id itemId,"
			+ "ts.zhname destName,tc.t_country_name countryName,"
			+ "ts.zhname title,ts.summary,ti.source,ti.small  "
			+ "from tb_home_item th join tb_home_category tb on "
			+ "tb.t_category_id = th.t_category_id "
			+ " join tb_dest ts on ts.dest_id = th.t_dest_id "
			+ "and ts.dest_id = th.t_link_id "
			+ " join tb_image ti on ti.image_id=ts.image_id "
			+ " join tb_country tc on tc.t_country_id = th.t_country_id "
			+ "where th.t_category_id =:1")
	public List<Map> findListDestHomeItem(int categoryId);

	@SuppressWarnings("rawtypes")
	@SQL("select tb.t_category_name categoryName,"
			+ "th.t_item_create_time itemCreateTime,th.t_item_id itemId,"
			+ "ts.zhname destName,tc.t_country_name countryName,"
			+ "thol.summary," + "thol.zhname title," + "ti.source,ti.small  "
			+ "from tb_home_item th  join tb_home_category tb on "
			+ "tb.t_category_id = th.t_category_id  join "
			+ "tb_hotel2 thol on thol.hotel_id=th.t_link_id "
			+ " join tb_dest ts on ts.dest_id = th.t_dest_id "
			+ " join tb_image ti on ti.image_id = thol.image_id  "
			+ " join tb_country tc on tc.t_country_id = th.t_country_id "
			+ "where th.t_category_id =:1")
	public List<Map> findListHotelHomeItem(int categoryId);

	@SuppressWarnings("rawtypes")
	@SQL("select tb.t_category_name categoryName,"
			+ "th.t_item_create_time itemCreateTime,th.t_item_id itemId,"
			+ "ts.zhname destName,tc.t_country_name countryName,"
			+ " td.summary,td.zhname title," + "ti.source,ti.small  "
			+ "from tb_home_item th  join tb_home_category tb on "
			+ "tb.t_category_id = th.t_category_id  join "
			+ "tb_dest_feature_item2 td on td.feature_item_id=th.t_link_id "
			+ " join tb_image ti on ti.image_id = td.image_id "
			+ " join tb_dest ts on ts.dest_id = th.t_dest_id "
			+ "  join tb_country tc on tc.t_country_id = th.t_country_id "
			+ "where th.t_category_id =:1")
	public List<Map> findListHomeItem(int categoryId);

	@SQL("select count(t_category_id) from tb_home_category "
			+ " where t_feature_category_id =:1")
	public int findHomeCategoryByCategoryId(int featureCategoryId);

//	@SQL("select * from tb_dest_feature_category where t_feature_category_id =:1 ")
//	public DestFeatureCategoryPOJO findDestFeatureCategoryById(
//			int featureCategoryId);

	@SuppressWarnings("rawtypes")
	@SQL("select * from (select "
			+ " hc.t_feature_category_id,"
			+ " hc.t_category_name,"
			+ " hc.t_category_update_time,"
			+ " hi.t_link_id,"
			+ " hi.t_dest_id,"
			+ " hi.t_item_create_time,"
			+ " dfi.zhname,"
			+ " dfi.enname,"
			+ " dfi.summary,"
			+ " t3.source,"
			+ " t3.small,t3.normal"
			+ " from tb_home_category hc"
			+ " join tb_home_item hi ON hc.t_category_id = hi.t_category_id"
			+ " join tb_dest_feature_item2 dfi ON dfi.feature_item_id = hi.t_link_id"
			+ " join tb_image t3 on t3.image_id = dfi.image_id"
			+ " where hc.t_feature_category_id > 0 "
			+ " union all select "
			+ " hc.t_feature_category_id,"
			+ " hc.t_category_name,"
			+ " hc.t_category_update_time,"
			+ " hi.t_link_id,"
			+ " hi.t_dest_id,"
			+ " hi.t_item_create_time,"
			+ " td.zhname,td.enname,"
			+ " td.summary,"
			+ " t3.source,t3.small,t3.normal"
			+ " from tb_home_category hc"
			+ " join tb_home_item hi ON hc.t_category_id = hi.t_category_id"
			+ " join tb_dest td ON hi.t_dest_id = td.dest_id"
			+ " join tb_image t3 on t3.image_id = td.image_id "
			+ " where hc.t_feature_category_id = - 2 "
			+ " union all select "
			+ " hc.t_feature_category_id,"
			+ " hc.t_category_name,"
			+ " hc.t_category_update_time,"
			+ " hi.t_link_id,"
			+ " hi.t_dest_id,"
			+ " hi.t_item_create_time,"
			+ " h.zhname,h.enname,"
			+ " h.summary,"
			+ " t3.source,t3.small,t3.normal"
			+ " from tb_home_category hc"
			+ " join tb_home_item hi ON hc.t_category_id = hi.t_category_id"
			+ " join tb_hotel2 h ON hi.t_link_id = h.hotel_id"
			+ " join tb_image t3 on t3.image_id = h.image_id "
			+ " where hc.t_feature_category_id = - 3 "
			+ " union all select "
			+ " hc.t_feature_category_id,"
			+ " hc.t_category_name,"
			+ " hc.t_category_update_time,"
			+ " hi.t_link_id,"
			+ " hi.t_dest_id,"
			+ " hi.t_item_create_time,"
			+ " n.zhname,n.enname,"
			+ " n.summary,"
			+ " t3.source,t3.small,t3.normal"
			+ " from tb_home_category hc"
			+ " join tb_home_item hi ON hc.t_category_id = hi.t_category_id"
			+ " join tb_news2 n ON n.news_id = hi.t_link_id"
			+ " join tb_image t3 on t3.image_id = n.image_id "
			+ " where"
			+ " hc.t_feature_category_id = - 1) t order by t.t_item_create_time desc")
	public List<Map> findAllHomeData();

}
